import getSqlPool from "./server.js"
import nodemailer from "nodemailer";
import sql from 'mssql'
import dotenv from "dotenv";

dotenv.config();

export async function changePass(username, password, email) {
  const pool = getSqlPool();
  try {
    // 执行 UPDATE 查询
    const result = await pool.request()
      .input('email', sql.NVarChar, email)
      .input('username', sql.NVarChar, username)
      .input('password', sql.NVarChar, password)
      .query('UPDATE zhanghao SET password = @password WHERE email = @email AND username = @username');

    // 检查是否有行被更新
    if (result.rowsAffected[0] === 0) {
      throw new Error('用户和邮箱不匹配');
    }

    console.log('密码更改成功');
    return { message: '密码更改成功' };  // 返回成功消息
  } catch (err) {
    console.error('密码更改失败:', err.message); // 输出详细的错误信息
    throw new Error('密码更改失败：' + err.message);  // 抛出详细的错误信息
  }
}

export async function vCode(email, inputCode) {
  const pool = getSqlPool();
  try {

    const request = await pool.request()
      .input('email', sql.NVarChar, email)
      .query('SELECT LTRIM(RTRIM(code)) AS code from VerificationCodes WHERE email = @email')

    const user = request.recordset[0];
    if(!user){
      return false;
    }

    const code = user.code;

    console.log("1" + inputCode + "/")
    console.log("2" + code + "/");


    if (inputCode === code) {
      return true;
    }
    else {
      return false;
    }
  }
  catch (error) {
    console.error('验证验证码失败:', error);
  }

}

export function create_VerificationCode() {
  return (Math.round(Math.random() * 1000000)).toString(); // 生成一个六位数验证码 math.round 四舍五入
}



export async function zhanghao_insertData(name, password, email) {
  try {
    const pool = getSqlPool();

    const result = await pool.request()
      .input('username', sql.NVarChar, name)
      .input('password', sql.NVarChar, password)
      .input('email', sql.NVarChar, email)
      .query('INSERT INTO zhanghao (username, password,email) VALUES (@username, @password,@email)');

    console.log(name + ',用户账号注册成功:', result.rowsAffected);
  }
  catch (err) {
    console.error('插入数据时发生错误:', err);
    if (err.number === 2627 || err.number === 2601) {
      console.error('错误信息:', err.message);
      // 这里可以根据需求返回友好的错误消息
      console.log('用户名或者邮箱已被使用');
      throw new Error('用户名或者邮箱已被使用');

    }
  }
}


export async function login(username, password) {
  console.log("2");



  try {
    const pool = getSqlPool();
    console.log(username + "/" + password);
    console.log("22");

    const result = await pool.request()
      .input('username', sql.NVarChar, username)
      .input('password', sql.NVarChar, password)
      .query('SELECT LTRIM(RTRIM(password)) AS password  from zhanghao where username=@username')

    const user = result.recordset[0];




    if (!user) {
      throw new Error("用户不存在")
    }

    const truePass = user.password;
    console.log(truePass + "///" + password + "/");

    if (truePass === password) {


      console.log("t");


      // 返回登录成功的信息和 token
      return true;
    }

    else {
      console.log("f")
      return false;
    }

  }
  catch (err) {
    // 如果发生任何错误，捕获并打印错误
    console.error('11登录失败:', err.message);

    // 返回错误信息
    return false;
  }
}

export async function checkLastGetCodeTime(email) {
  try {
    const pool = getSqlPool();

    const lastGetCodeTime = new Date();

    const code = create_VerificationCode();

    const request = await pool.request()
      .input('email', sql.NVarChar, email)
      .query('SELECT email FROM VerificationCodes WHERE email = @email')

    if (request.recordset.length > 0) {

      // 获取查询到的第一条记录
      const record = request.recordset[0];

      // 赋值 email 和 lastGetCodeTime
      const queriedEmail = record.email;
      const lastTime = new Date(record.lastGetCodeTime);

      const timeDifference = lastGetCodeTime - lastTime;  // 时间差（毫秒）
      const oneHourInMillis = 5 * 60 * 1000;  // 5分钟的毫秒数

      // 如果时间差超过5分钟，允许重新获取验证码
      if (timeDifference > oneHourInMillis) {

        // 调用 inputVerificationCode 函数，将验证码和时间传入数据库
        await inputVerificationCode(queriedEmail, code, lastGetCodeTime);

        console.log('验证码已生成并存储到数据库');
      } else {
        console.log('1222');
        throw new Error('验证码获取时间间隔太短，请稍后再试');
      }
    } else {
      await inputVerificationCode(email, code, lastGetCodeTime);
    }
  }

  catch (err) {
    console.error('查询失败:', err);
  }
}

async function inputVerificationCode(email, code, lastGetCodeTime, ip) {
  try {

    const transporter = nodemailer.createTransport({
      host: 'smtp.163.com',  // 使用 163 邮箱
      port: 465,
      secure: true,
      auth: {
        user: process.env.EMAIL_USER, // 你的 163 邮箱地址
        pass: process.env.EMAIL_PASS  //163 邮箱授权码
      }
    });

    const mailOptions = {
      from: process.env.EMAIL_USER,  // 发件人
      to: email,  // 收件人
      subject: '邮箱验证码',  // 邮件主题
      text: `您的验证码是：${code},一直有效,五分钟后可重发并重置`  // 邮件内容
    };

    // 使用 Nodemailer 发送邮件
    const info = await transporter.sendMail(mailOptions); // 确保使用 await 来发送邮件

    // 输出日志
    console.log('验证码发送成功:', info.response);

    const pool = getSqlPool();

    const result = await pool.request()
      .input('email', sql.NVarChar, email)
      .input('code', sql.NVarChar, code)
      .input('lastGetCodeTime', sql.DateTime, lastGetCodeTime)
      .query(`
        if EXISTS (select 1 from VerificationCodes  where email = @email )
          begin
            update VerificationCodes set lastGetCodeTime=@lastGetCodeTime where email = @email
          end
        else
          begin
            insert into VerificationCodes (email,code,lastGetCodeTime) values (@email,@code,@lastGetCodeTime)
          end
            `);

    console.log(email + ' 更新验证码成功:', result.rowsAffected);

  }
  catch (err) {
    console.log('更新验证码失败：', err);
  }
}
export async function updateLoginTime(ip, lastLoginTime) {
  try {



    const pool = getSqlPool();

    const result = await pool.request()
      .input('ip', sql.NVarChar, ip


      )
      .input('lastLoginTime', sql.DateTime, lastLoginTime)
      .query(`
          if EXISTS (select 1 from loginTimeOut  where ip = @ip )
            begin
              update loginTimeOut set lastLoginTime=@lastLoginTime  where ip = @ip
            end
          else
            begin
              insert into loginTimeOut (ip,lastLoginTime) values (@ip,@lastLoginTime)
            end
              `);

    console.log(ip + ' 更新上一次登录时间成功:', result.rowsAffected);
  }
  catch (err) {
    console.error(ip + '更新上一次登录时间失败')
  }
}

export async function updateRegisterTimeOut(ip, getCodeTime, getRegister) {
  try {
    const pool = getSqlPool();

    const result = await pool.request()
      .input('ip', sql.NVarChar, ip)
      .input('getCodeTime', sql.DateTime, getCodeTime)
      .input('getRegister', sql.DateTime, getRegister)
      .query(`
        if EXISTS (select 1 from registerTimeOut  where ip = @ip )
          begin
            update registerTimeOut set 
            getCodeTime=@getCodeTime, getRegister=@getRegister 
            where ip = @ip
          end
        else
          begin
            insert into registerTimeOut (ip,getCodeTime,getRegister) values (@ip,@getCodeTime,@getRegister)
          end
            `);
    console.log(ip + ' 更新注册时间成功:', result.rowsAffected);
  }
  catch (err) {
    console.log("更新注册时间失败：", err);
  }
}

export async function queryDatabase() {
  try {
    // 连接到 SQL Server
    const pool = getSqlPool();

    console.log('连接成功!');

    // 执行查询
    const result = await pool.request().query('SELECT * FROM zhanghao');

    // 输出查询结果
    console.log(result.recordset);  // 查询结果存储在 recordset 中
  } catch (err) {
    console.error('连接或查询出错:', err);
  }
}

export async function deleteZhangHao() {
  try {
    // 连接到 SQL Server
    let pool = getSqlPool();

    console.log('连接成功!');

    // 执行删除命令
    const result = await pool.request().query('delete from zhanghao');

    // 删除表数据
    console.log('账号表数据删除成功');  // 删除成功输出控制台
  } catch (err) {
    console.error('删除数据时出错', err);
  }
}

export async function delete_loginTimeOut() {
  try {
    // 连接到 SQL Server
    let pool = getSqlPool();

    console.log('连接成功!');

    // 执行删除命令
    const result = await pool.request().query('delete from loginTimeOut');

    // 删除表数据
    console.log('登录时间表数据删除成功');  // 删除成功输出控制台
  } catch (err) {
    console.error('删除数据时出错', err);
  }
}

export async function delete_registerTimeOut() {
  try {
    // 连接到 SQL Server
    let pool = getSqlPool();

    console.log('连接成功!');

    // 执行删除命令
    const result = await pool.request().query('delete from registerTimeOut');

    // 删除表数据
    console.log('注册时间表数据删除成功');  // 删除成功输出控制台
  } catch (err) {
    console.error('删除数据时出错', err);
  }
}
export async function delete_VerificationCodes() {
  try {
    // 连接到 SQL Server
    let pool = getSqlPool();

    console.log('连接成功!');

    // 执行删除命令
    const result = await pool.request().query('delete from VerificationCodes');

    // 删除表数据
    console.log('验证码表数据删除成功');  // 删除成功输出控制台
  } catch (err) {
    console.error('删除数据时出错', err);
  }
}









